﻿<%@ WebHandler Language="C#" Class="EmployeeMaintPage" %>

using System;
using System.Web;
using BusinessRule.DTO;
using System.Web.SessionState;
using DatabaseAccess.DBUtility;
using System.Data.OracleClient;
using System.Data;
using System.Linq;
using System.Collections;
using System.Collections.Generic;
using Newtonsoft.Json;
using BusinessRule.Business;
using BusinessRule.IBusiness;
using BusinessRule.BusinessFactory;

public class EmployeeMaintPage : IHttpHandler, IRequiresSessionState
{
    CurrentUserInfo m_UserInfo;
    uLMSCommonBusiness commonBusiness = new uLMSCommonBusiness();
    EmployeeMaintPageBusiness employeeMaintBusiness = new EmployeeMaintPageBusiness();
    public void ProcessRequest(HttpContext context)
    {
        try
        {
            var action = context.Request["action"].ToString();
            m_UserInfo = (CurrentUserInfo)context.Session["CurrentUserInfo"];
            switch (action)
            {
                case "GetFactory":
                    GetFactory(context);//查询分厂
                    break;
                case "GetWorkcenter":
                    GetWorkcenter(context);//查询工区列表
                    break;
                case "GetTeam":
                    GetTeam(context);//查询班组
                    break;
                case "GetCalender":
                    GetCalender(context);//查询日历
                    break;
                case "GetRole":
                    GetRole(context);
                    break;
                case "GetResourceFamily":
                    GetResourceFamily(context);
                    break;
                case "GetResourceList":
                    GetResourceList(context);
                    break;
                case "GetWorkshift":
                    GetWorkshift(context);
                    break;
                case "GetEmployee":
                    GetEmployee(context);
                    break;
                case "UpdateEmployeeInfo":
                    UpdateEmployeeInfo(context);
                    break;
                case "GetEmployeeResources":
                    GetEmployeeResources(context);//查询用户设备信息
                    break;
                case "SaveEmployeeResource":
                    SaveEmployeeResource(context);
                    break;
                case "GetEmployeeLeaveInfo":
                    GetEmployeeLeaveInfo(context);//查询用户休假信息 
                    break;
                case "SaveEmployeeLeaveInfo":
                    SaveEmployeeLeaveInfo(context);//保存用户休假信息 
                    break;
                case "DeleteEmployeeLeaveInfo":
                    DeleteEmployeeLeaveInfo(context);//删除用户休假信息 
                    break;
                default:
                    break;
            }
        }
        catch (Exception ex)
        {
            var result = new uMESProcessResult();
            result.Result = false;
            result.Message = ex.Message;
            context.Response.ContentType = "text/html";

            context.Response.Write(JsonConvert.SerializeObject(result));
        }
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }

    // 查询用户
    void GetEmployee(HttpContext context)
    {
        string strSql = @"
SELECT e.employeeid,e.employeename,e.fullname,e.cardnum,f.factoryname,f.factoryid,w.workcentername,w.workcenterid,te.teamname,te.teamid,
       er.rolenames,wks.workshiftname,wks.workshiftid,
       cld.makecalendarname,cld.makecalendarid,e.skillnum,e.IsParallel,e.isschedule
FROM employee e
LEFT JOIN Sessionvalues sv ON sv.employeeid = e.employeeid
LEFT JOIN factory f ON f.factoryid = sv.factoryid
LEFT JOIN Workcenter w ON w.workcenterid = sv.workcenterid
LEFT JOIN Workshift wks ON wks.workshiftid = e.workshiftid
LEFT JOIN Makecalendar cld ON cld.makecalendarid = e.makecalendarid 
LEFT JOIN ( SELECT e.employeeid,wm_concat(r.rolename) AS rolenames  
            FROM employeerole er 
            LEFT JOIN employee e ON e.employeeid = er.employeeid  
            LEFT JOIN roledef r ON r.roleid = er.roleid  
            WHERE r.rolename NOT IN ('Login','SPC','Mfg Audit Trail Inquiry','Portal Configuration','Security Administration')
                  AND r.rolename NOT LIKE 'Default%'
            GROUP BY e.employeeid  
          ) er ON er.employeeid = e.employeeid 
LEFT JOIN ( SELECT  t.teamname,t.teamid,te.employeeid
            FROM teamemployee te 
            LEFT JOIN team t  ON t.teamid = te.teamid 
          ) te  ON te.employeeid = e.employeeid
WHERE 1=1 
";
        if (context.Request["factoryid"] != null && context.Request["factoryid"].ToString() != "")
        {
            strSql += string.Format(" AND f.factoryid = '{0}'", context.Request["factoryid"].ToString());
        }
        if (context.Request["workcenterid"] != null && context.Request["workcenterid"].ToString() != "")
        {
            strSql += string.Format(" AND w.workcenterid = '{0}'", context.Request["workcenterid"].ToString());
        }
        if (context.Request["teamid"] != null && context.Request["teamid"].ToString() != "")
        {
            strSql += string.Format(" AND te.teamid = '{0}'", context.Request["teamid"].ToString());
        }
        if (context.Request["rolename"] != null && context.Request["rolename"].ToString() != "")
        {
            strSql += string.Format(" AND er.rolenames like '%{0}%'", context.Request["rolename"].ToString());
        }
        if (context.Request["fullname"] != null && context.Request["fullname"].ToString() != "")
        {
            strSql += string.Format(" AND e.fullname like '%{0}%'", context.Request["fullname"].ToString());
        }
        if (context.Request["employeename"] != null && context.Request["employeename"].ToString() != "")
        {
            strSql += string.Format(" AND e.employeename like '%{0}%'", context.Request["employeename"].ToString());
        }

        strSql += " ORDER BY e.employeename ";

        uMESPagingDataDTO paraPage = new uMESPagingDataDTO();
        paraPage.CurrentPageIndex = Convert.ToInt32(context.Request.Form["pageNumber"].ToString());//页码
        paraPage.PageSize = Convert.ToInt32(context.Request.Form["PageSize"].ToString());//每页页数
        paraPage.strSQL = strSql;
        uMESProcessResult result = DatabaseHelper.RunProcedureForPage(paraPage);
        foreach (DataColumn col in result.DBTable.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        context.Response.Write(JsonConvert.SerializeObject(result));
    }

    //获取分厂
    void GetFactory(HttpContext context)
    {
        string strSql = @"
SELECT f.factoryid,f.factoryname,u.attributevalue
FROM factory f
LEFT JOIN Userattribute u ON u.parentid = f.factoryid AND u.userattributename = '分厂代码'
WHERE 1=1 
";
        DataTable dt = DatabaseHelper.Query_ReturnDatTable(strSql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = true, Message = "获取成功", DBTable = dt }));
    }

    //获取角色列表
    void GetRole(HttpContext context)
    {
        string strSql = @"
SELECT rd.rolename 
FROM roledef rd 
WHERE rd.rolename NOT IN ('Login','SPC','Mfg Audit Trail Inquiry','Portal Configuration','Security Administration') 
      AND rd.rolename NOT LIKE 'Default%'
";
        DataTable dt = DatabaseHelper.Query_ReturnDatTable(strSql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = true, Message = "获取成功", DBTable = dt }));
    }

    //获取工区
    void GetWorkcenter(HttpContext context)
    {
        string strSql = @"SELECT w.workcenterid,w.workcentername,w.notes,w.wctype,w.description FROM Workcenter w ";
        if (context.Request["factoryid"] != null && context.Request["factoryid"].ToString() != "全部")
        {
            strSql += string.Format(" WHERE w.factoryid = '{0}'", context.Request["factoryid"].ToString());
        }
        DataTable dt = DatabaseHelper.Query_ReturnDatTable(strSql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = true, Message = "获取成功", DBTable = dt }));
    }

    //获取班组
    void GetTeam(HttpContext context)
    {
        string strSql = @"SELECT t.teamid,t.teamname FROM team t";
        if (context.Request["workcenterid"] != null && context.Request["workcenterid"].ToString() != "")
        {
            strSql += string.Format(" WHERE t.workcenterid = '{0}'", context.Request["workcenterid"].ToString());
        }
        DataTable dt = DatabaseHelper.Query_ReturnDatTable(strSql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = true, Message = "获取成功", DBTable = dt }));
    }

    //获取班次
    void GetWorkshift(HttpContext context)
    {
        string sql = @"SELECT wks.workshiftid,wks.workshiftname FROM workshift wks";
        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = true, Message = "获取成功", DBTable = dt }));
    }

    //获取日历
    void GetCalender(HttpContext context)
    {
        string sql = @"SELECT cld.makecalendarid,cld.makecalendarname,cld.description,cld.notes FROM Makecalendar cld";
        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = true, Message = "获取成功", DBTable = dt }));
    }

    //获取设备型号
    void GetResourceFamily(HttpContext context)
    {
        string sql = @"SELECT rf.resourcefamilyid,rf.resourcefamilyname FROM Resourcefamily rf";
        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = true, Message = "获取成功", DBTable = dt }));
    }

    //查询设备列表
    void GetResourceList(HttpContext context)
    {
        string sql = @"
SELECT rd.resourceid,rd.resourcename,rd.description,rd.notes,rf.resourcefamilyname,rf.resourcefamilyid
FROM Resourcedef rd
LEFT JOIN Resourcefamily rf ON rf.resourcefamilyid = rd.resourcefamilyid
LEFT JOIN factory f ON f.factoryid = rd.factoryid
WHERE 1=1 
";
        //设备编码
        if (context.Request["resourcename"] != null && context.Request["resourcename"].ToString() != "")
        {
            sql += string.Format(" AND rd.resourcename LIKE '%{0}%'", context.Request["resourcename"].ToString());
        }
        //设备类型
        if (context.Request["resourcefamilyid"] != null && context.Request["resourcefamilyid"].ToString() != "")
        {
            sql += string.Format(" AND rf.resourcefamilyid = '{0}'", context.Request["resourcefamilyid"].ToString());
        }
        //设备名称
        if (context.Request["description"] != null && context.Request["description"].ToString() != "")
        {
            sql += string.Format(" AND rd.description LIKE '%{0}%'", context.Request["description"].ToString());
        }
        if (context.Request["factoryid"] != null && context.Request["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND f.factoryid = '{0}'", context.Request["factoryid"].ToString());
        }
        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = true, Message = "获取成功", DBTable = dt }));
    }

    string GetEmployeeResourcesSql(HttpContext context)
    {
        var dtPostData = JsonConvert.DeserializeObject<DataTable>(context.Request.Form["postData"].ToString());
        DataTable dtEmployee = dtPostData.DefaultView.ToTable(true, "employeeid");
        string strQueryList = "(";
        foreach (DataRow dr in dtEmployee.Rows)
        {
            strQueryList += string.Format("'{0}',", dr["employeeid"].ToString());
        }
        if (strQueryList != "(")
        {
            strQueryList = strQueryList.Substring(0, strQueryList.Length - 1) + ")";
        }
        else
        {
            strQueryList = "('')";
        }
        string sql = @"SELECT e.employeeid,e.fullname,e.employeename,rd.resourceid,rd.resourcename,rd.description,rd.notes,rf.resourcefamilyid,rf.resourcefamilyname
                    FROM employeeresourcelist erl 
                    LEFT JOIN Resourcedef rd ON rd.resourceid = erl.resourcelistid 
                    LEFT JOIN employee e ON e.employeeid = erl.employeeid
                    LEFT JOIN Resourcefamily rf ON rf.resourcefamilyid = rd.resourcefamilyid
                    WHERE erl.employeeid IN {0}";
        sql = string.Format(sql, strQueryList);
        return sql;
    }

    //查询员工当前已关联设备
    void GetEmployeeResources(HttpContext context)
    {
        string sql = GetEmployeeResourcesSql(context);
        DataTable dt = DatabaseHelper.Query_ReturnDatTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = true, Message = "", DBTable = dt }));
    }

    //更新员工班次、日历、能力信息
    void UpdateEmployeeInfo(HttpContext context)
    {
        var dtPostData = JsonConvert.DeserializeObject<DataTable>(context.Request.Form["postData"].ToString());
        if (m_UserInfo == null)
        {
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = false, Message = "当前未登录", DBTable = null }));
        }
        else
        {
            DataTable dtResult = new DataTable();
            dtResult.Columns.Add("employeename", typeof(string));
            dtResult.Columns.Add("result", typeof(string));

            foreach (DataRow dr in dtPostData.Rows)
            {
                Dictionary<string, string> para = new Dictionary<string, string>();
                para["employeename"] = dr["employeename"].ToString();
                para["workshiftid"] = dr["workshiftid"].ToString();
                para["makecalendarid"] = dr["makecalendarid"].ToString();
                para["skillnum"] = dr["skillnum"].ToString();
                para["userName"] = m_UserInfo.APIUserName.ToString();
                para["passWord"] = m_UserInfo.APIPassword.ToString();
                para["isParallel"] = dr["isParallel"].ToString();
                para["isSchedule"] = dr["isSchedule"].ToString(); //add by zhangrj 2023-03-29
                string msg = "";
                bool result = employeeMaintBusiness.UpdateEmployeeInfo(para, ref msg);

                DataRow row = dtResult.NewRow();
                row["employeename"] = dr["employeename"].ToString();
                row["result"] = msg;
                dtResult.Rows.Add(row);
            }
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = true, Message = "", DBTable = dtResult }));
        }
    }

    //更新用户管理设备信息
    void SaveEmployeeResource(HttpContext context)
    {
        var dtPostData = JsonConvert.DeserializeObject<DataTable>(context.Request.Form["postData"].ToString());
        if (m_UserInfo == null)
        {
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = false, Message = "当前未登录", DBTable = null }));
        }
        else
        {

            DataTable dtEmployee = dtPostData.DefaultView.ToTable(true, "employeename", "employeeid");
            DataTable dtResource = new DataTable();
            string sql = "";

            foreach (DataRow dr in dtEmployee.Rows)
            {
                Dictionary<string, string> para = new Dictionary<string, string>();
                para["employeename"] = dr["employeename"].ToString();
                para["userName"] = m_UserInfo.APIUserName.ToString();
                para["passWord"] = m_UserInfo.APIPassword.ToString();

                dtResource = dtPostData.Clone();
                DataRow[] drs = dtPostData.Select(string.Format("employeename = '{0}'", dr["employeename"].ToString()));

                foreach (DataRow item in drs)
                {
                    if (item["resourceid"] != null && item["resourceid"].ToString() != "")
                    {
                        dtResource.Rows.Add(item.ItemArray);
                    }
                }

                sql = string.Format("SELECT COUNT(1) qty FROM employeeresourcelist er WHERE er.employeeid = '{0}' ", dr["employeeid"].ToString());
                para["resourceCount"] = DatabaseHelper.Query_ReturnDatTable(sql).Rows[0]["qty"].ToString();

                string msg = "";
                bool result = employeeMaintBusiness.SaveEmployeeResource(para, dtResource, ref msg);
            }

            sql = GetEmployeeResourcesSql(context);
            DataTable dtResult = DatabaseHelper.Query_ReturnDatTable(sql);
            foreach (DataColumn col in dtResult.Columns)
            {
                col.ColumnName = col.ColumnName.ToLower();
            }

            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = true, Message = "", DBTable = dtResult }));
        }
    }

    //查询用户休假信息
    void GetEmployeeLeaveInfo(HttpContext context)
    {
        if (m_UserInfo == null)
        {
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = false, Message = "当前未登录", DBTable = null }));
        }
        else
        {
            string sql = @"
SELECT t.employeeleaveinfoid,t.cause,t.leaveenddate,t.leavestartdate,e.fullname,e.employeename,e.employeeid,t.createdate,e2.fullname creater
FROM employeeleaveinfo t
LEFT JOIN employee e ON t.employeeid = e.employeeid
LEFT JOIN employee e2 ON e2.employeeid = t.createemployeeid
WHERE t.employeeid = '{0}'";
            sql = string.Format(sql, context.Request["employeeid"].ToString());
            
            uMESPagingDataDTO paraPage = new uMESPagingDataDTO();
            paraPage.CurrentPageIndex = Convert.ToInt32(context.Request.Form["pageNumber"].ToString());//页码
            paraPage.PageSize = Convert.ToInt32(context.Request.Form["PageSize"].ToString());//每页页数
            paraPage.strSQL = sql;
            uMESProcessResult result = DatabaseHelper.RunProcedureForPage(paraPage);
            foreach (DataColumn col in result.DBTable.Columns)
            {
                col.ColumnName = col.ColumnName.ToLower();
            }
            context.Response.Write(JsonConvert.SerializeObject(result));
        }
    }

    //保存用户休假信息
    void SaveEmployeeLeaveInfo(HttpContext context)
    {
        if (m_UserInfo == null)
        {
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = false, Message = "当前未登录", DBTable = null }));
        }
        else
        {
            string sql = @"
INSERT INTO employeeleaveinfo(cause,createdate,createemployeeid,employeeid,employeeleaveinfoid,leaveenddate,leavestartdate)
VALUES('{0}',SYSDATE,'{1}','{2}','{3}',to_date('{4}','yyyy/mm/dd hh24:mi:ss'),to_date('{5}','yyyy/mm/dd hh24:mi:ss'))";

            string cause = context.Request["cause"].ToString();
            string createemployeeid = m_UserInfo.EmployeeID.ToString();
            string employeeid = context.Request["employeeid"].ToString();
            string employeeleaveinfoid = Guid.NewGuid().ToString();
            string leaveenddate = context.Request["leaveenddate"].ToString();
            string leavestartdate = context.Request["leavestartdate"].ToString();
            sql = string.Format(sql, cause, createemployeeid, employeeid, employeeleaveinfoid, leaveenddate, leavestartdate);
            int insertQty = DatabaseHelper.ExecuteSql(sql);
            if (insertQty > 0)
            {
                context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = true, Message = "添加成功" }));
            }
            else
            {
                context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = false, Message = "添加失败" }));
            }
        }
    }

    //删除用户休假信息
    void DeleteEmployeeLeaveInfo(HttpContext context)
    {
        if (m_UserInfo == null)
        {
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = false, Message = "当前未登录", DBTable = null }));
        }
        else
        {
            var dtPostData = JsonConvert.DeserializeObject<DataTable>(context.Request.Form["postData"].ToString());
            ArrayList SQLStringList = new ArrayList();
            foreach (DataRow dr in dtPostData.Rows)
            {
                string employeeleaveinfoid = dr["employeeleaveinfoid"].ToString();
                SQLStringList.Add(string.Format(" DELETE employeeleaveinfo t WHERE t.employeeleaveinfoid = '{0}'", employeeleaveinfoid));
            }
            Boolean isSuccess =  DatabaseHelper.ExecuteSqlListTran(SQLStringList);
            if (isSuccess)
            {
                context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = true, Message = "删除成功" }));
            }
            else
            {
                context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = false, Message = "删除失败" }));
            }
        }
    }
}